<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"/>
    <title>SqlMan/Query Concept</title>
    <meta name="description" content="TODO"/>
    <meta name="keywords" content="SqlMan, document, java sql, java jdbc, java orm, jdbc tools, jdbc helper, last one"/>
    <meta name="viewport" content="width=device-width, initial-scale=1"/>
    <link rel="stylesheet" href="https://framework.ajaxjs.com/static/new-ui/css/common.css"/>
    <link rel="stylesheet" href="https://iam.ajaxjs.com/asset/main.css"/>
    <link rel="icon" type="image/png" href="/asset/favicon.png"/>
    <script src="https://framework.ajaxjs.com/static/aj-docs/common.js"></script>
    <script>
            // 获取用户的默认语言
            var userLang = navigator.language || navigator.userLanguage;

            // 检查是否为中文环境（包括简体和繁体）
            if (userLang.startsWith('zh') && location.pathname.indexOf('cn') == -1) {
                 confirm('欢迎！您可以改为访问中文内容。是否继续？') && location.assign('/docs/cn');  // 如果是中文，则弹出提示
            }

            var _hmt = _hmt || [];
            (function() {
              var hm = document.createElement("script");
              hm.src = "https://hm.baidu.com/hm.js?3cb62106ad945fc91efddfe250e1542e";
              var s = document.getElementsByTagName("script")[0];
              s.parentNode.insertBefore(hm, s);
            })();

    </script>
</head>
<body>
<nav>
    <div>
        <div class="links">
            <a href="/">🏠 Home</a>
            |
            ⚙️ Source:
            <a target="_blank" href="https://github.com/lightweight-component/SqlMan">Github</a>/<a target="_blank" href="https://gitcode.com/lightweight-component/SqlMan">Gitcode</a>

            | <a href="/docs/cn">Chinese Version</a>
        </div>
        <h1>
            <img src="/asset/favicon-2x.png" style="vertical-align: middle;height: 45px;margin-bottom: 6px;"/> SqlMan
        </h1>
        <h3>Lightweight Data Access Tools -- User Manual
        </h3>
    </div>
</nav>
<div>
    <menu>

        <ul>
            <li class="selected">
                <a href="/docs">Home</a>
            </li>
            <li>
                <a href="/docs/quick-start">Quick Start</a>
            </li>
            <li>
                <a href="/docs/setup">Setup</a>
            </li>
        </ul>
        <h3>Querying the Database</h3>
        <ul>
            <li>
                <a href="/docs/query/query-concept">Query Concept</a>
            </li>
            <li>
                <a href="/docs/query/query-tutorial">Query Tutorial</a>
            </li>
            <li>
                <a href="/docs/query/query-paging">Paging Query</a>
            </li>
        </ul>
        <h3>Writing the data</h3>
        <ul>
            <li>
                <a href="/docs/write/Create-Update-Delete">Create/Update/Delete</a></li>
            <li>
                <a href="/docs/write/batch">Batch Update</a>
            </li>
        </ul>
        <h3>Other Usage</h3>
        <ul>
            <li>
                <a href="/docs/other/sql-xml">SQL in XML</a></li>
            <li>
                <a href="/docs/other/entity">No SQL but Entity</a>
            </li>
        </ul>
        <h3>Misc</h3>
        <ul>
            <li><a href="/docs/common/versions">Release History</a></li>
            <li><a href="/docs/common/contact">Contact</a></li>
        </ul>
    </menu>
    <article class="aj-text">
        <h1>SQL in XML</h1>
        <h2>Why SQL in XML?</h2>
        <p>Like the famous framework MyBatis, SQL statements can be stored in XML files. There are several reasons for this approach. Here are some advantages of storing SQL in XML:</p>
        <ol>
            <li><strong>Separation of Concerns</strong>: Keeping SQL statements in XML files helps separate the SQL logic from the Java code. This makes the codebase cleaner and easier to manage.</li>
            <li><strong>Readability</strong>: Long SQL statements can be more readable when stored in XML. The structure of the XML file can help make the SQL queries more organized and easier to understand.</li>
            <li><strong>Maintainability</strong>: When SQL statements are stored in XML, it is easier to update and maintain them without changing the Java code. This is particularly useful for large projects with many
                SQL queries.
            </li>
        </ol>
        <p>SqlMan follows a similar approach to MyBatis in handling SQL statements.</p>
        <h2>How to use SQL in XML?</h2>
        <p>Before we begin, we should have the SQL fragments defined in an XML file, named <code>sql.xml</code>, stored in the classpath <code>/sql</code>.</p>
        <pre><code class="language-xml">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;root&gt;
    &lt;sql id=&quot;foo&quot;&gt;SELECT COUNT(*) AS total FROM shop_address&lt;/sql&gt;
    &lt;sql id=&quot;foo-2&quot;&gt;SELECT COUNT(*) AS total FROM shop_address WHERE id = ?&lt;/sql&gt;
    &lt;sql id=&quot;foo-3&quot;&gt;SELECT id FROM ${tableName} WHERE id = #{stat}&lt;/sql&gt;
    &lt;sql id=&quot;foo-4&quot;&gt;SELECT * FROM ${tableName} WHERE id = ?&lt;/sql&gt;
    &lt;sql id=&quot;foo-5&quot;&gt;
        &lt;if test=&quot;type=='address'&quot;&gt;
            SELECT COUNT(*) AS total FROM shop_address
        &lt;/if&gt;
        &lt;if test=&quot;type=='article'&quot;&gt;
            SELECT COUNT(*) AS total FROM ${tableName}
        &lt;/if&gt;
    &lt;/sql&gt;
    &lt;sql id=&quot;foo-6&quot;&gt;SELECT id FROM ${tableName} WHERE ${T(com.ajaxjs.sqlman.sql.TestXml).w()}&lt;/sql&gt;
&lt;/root&gt;
</code></pre>
        <p>This method executes an SQL statement defined in an XML fragment by its identifier (<code>sqlId</code>) and returns a DAO object containing the result.</p>
        <pre><code class="language-java">int result = new Sql(conn).inputXml(&quot;foo&quot;).queryOne(int.class); // fetch the first one
System.out.println(result);
assertTrue(result &gt; 0);
</code></pre>
        <p>Now that we’ve seen before, values, and parameters, we can go back to statements in XML file and apply the same knowledge.</p>
        <pre><code class="language-java">int result;
result = new Sql(conn).inputXml(&quot;foo-2&quot;, 1).queryOne(int.class);
assertEquals(1, result);

result = new Sql(conn).inputXml(&quot;foo-3&quot;, mapOf(&quot;tableName&quot;, &quot;shop_address&quot;, &quot;stat&quot;, 1)).queryOne(int.class);
assertEquals(1, result);

result = new Sql(conn).inputXml(&quot;foo-4&quot;, mapOf(&quot;tableName&quot;, &quot;shop_address&quot;, &quot;abc&quot;, 2), 1).queryOne(int.class);
System.out.println(result); // TODO, should be return 0
</code></pre>
        <p>This method executes an SQL statement defined in an XML fragment by its identifier (<code>sqlId</code>) with key-value pairs and variable parameters.</p>
        <pre><code class="language-java">/**
 * Executes an SQL statement defined in an XML fragment and returns a DAO object.
 *
 * @param sqlId     SQL identifier used to locate the specific SQL statement.
 * @param keyParams Key-value pair parameters used to replace variables in the SQL statement.
 * @param params    Variable parameters used to replace placeholders in the SQL statement.
 * @return A DAO object containing the data retrieved from the database.
 */
public DAO inputXml(String sqlId, Map&lt;String, Object&gt; keyParams, Object... params) {
    // Implementation to execute the SQL and return DAO object
}
</code></pre>
        <h2>Tag Support</h2>
        <p>For dynamic SQL generation, SqlMan supports the following tags:<code>IF</code>.</p>
        <pre><code class="language-xml">&lt;sql id=&quot;foo-5&quot;&gt;
    &lt;if test=&quot;type=='address'&quot;&gt;
        SELECT COUNT(*) AS total FROM shop_address
    &lt;/if&gt;
    &lt;if test=&quot;type=='article'&quot;&gt;
        SELECT COUNT(*) AS total FROM ${tableName}
    &lt;/if&gt;
&lt;/sql&gt;
</code></pre>
        <p>The value in <code>test</code> attribute is a expression, like <code>i &gt; 10</code>. It can be a simple boolean expression or a more complex expression that returns a boolean value. Any expression that
            matches the pattern of Spring Expression is illegal.</p>
        <p>More tags like <code>if...else</code>, <code>foreach</code> are on the way.</p>
        <h2>Calling Java Methods</h2>
        <p>Some tasks cloud not be accomplished by SQL alone. In such cases, it's better to call Java methods to do the job. SqlMan supports calling Java methods in SQL using the syntax <code>${T(com.ajaxjs.sqlman.sql.TestXml).w()}</code>.
        </p>
        <pre><code class="language-xml">&lt;sql id=&quot;foo-6&quot;&gt;SELECT id FROM ${tableName} WHERE ${T(com.ajaxjs.sqlman.sql.TestXml).w()}&lt;/sql&gt;
</code></pre>

    </article>
</div>
<footer>
    SqlMan, a part of <a href="https://framework.ajaxjs.com" target="_blank">AJ-Framework</a> open source. Mail:
    frank@ajaxjs.com, visit <a href="https://blog.csdn.net/zhangxin09" target="_blank">my blog(In Chinese)</a>.
    <br/>
    <br/>
    Copyright © 2025 Frank Cheung. All rights reserved.
    <script>
        var _hmt = _hmt || [];
        (function() {
          var hm = document.createElement("script");
          hm.src = "https://hm.baidu.com/hm.js?3cb62106ad945fc91efddfe250e1542e";
          var s = document.getElementsByTagName("script")[0];
          s.parentNode.insertBefore(hm, s);
        })();

    </script>
</footer>
</body>
</html>